import pymysql
import openpyxl

def db_handler(arr):
    # 1. 创建连接（Connection）
    conn = pymysql.connect(host='127.0.0.1', port=3306,
                        user='root', password='qwe123',
                        database='school', charset='utf8mb4')
    try:
        # 2. 获取游标对象（Cursor）
        with conn.cursor() as cursor:
            # 3. 通过游标对象向数据库服务器发出SQL语句
            for item in arr:
                sql=f"INSERT INTO tb_student (stu_id, stu_name,stu_sex,stu_birth,stu_addr,col_id) VALUES ({item[0]},\
                        '{item[1]}',{item[2]},'{item[3]}','{item[4]}',{item[5]})"
                print(f'sql:{sql}')
                cursor.execute(sql)
        conn.commit()
    except pymysql.MySQLError as err:
        # 4. 回滚事务
        conn.rollback()
        print(type(err), err)
    finally:
        # 5. 关闭连接释放资源
        conn.close()
#导出学生表为Excel文件，本地修改
#读取修改后的excel，如果已经有id的就修改，没有就新增

#读取文件
try:
    book=openpyxl.load_workbook('./tb_student.xlsx')
    sheet=book.active
    data=[[None] for _ in sheet.iter_rows()]
    rowIndex=0
    for line in sheet.iter_rows():
        colIndex=0
        for item in line:
            #不要每行最后一个单元格
            if colIndex==len(line)-1:
                continue
            if colIndex==0:
                data[rowIndex][0]=item.value
            else:
                data[rowIndex].append(item.value)
            colIndex+=1
        rowIndex+=1
    data.pop(0)#不要标题行
    db_handler(data)
except FileNotFoundError as e:
    print('指定的文件无法打开.')
except IOError as e:
    print('读写文件时出现错误.')
